﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using ExtBusiness;
using WF_Business;
using ChBusiness.Struct;

namespace ExtBusiness
{
    public class UploadFileOperation
    {
        private string _newId;

        /// <summary>
        /// 新生成的id
        /// </summary>
        public string NewId
        {
            get
            {
                return _newId;
            }
            set
            {
                _newId = value;
            }
        }
        
        /// <summary>
        /// 验证上传文件后缀是否有效
        /// </summary>
        /// <param name="strFileExtension">文件扩展名</param>
        /// <returns></returns>
        public bool HaveValideFileExtension(string strFileExtension)
        {
            bool bOk = false;
            string strUploadFileFormat = System.Configuration.ConfigurationManager.AppSettings["UploadFileFormat"].ToString();
            string[] arrUploadFileFormat = strUploadFileFormat.Split(',');

            for (int i = 0; i < arrUploadFileFormat.Length; i++)
            {
                if (strFileExtension.CompareTo(arrUploadFileFormat[i]) == 0)
                {
                    bOk = true;
                    break;
                }
            }

            return bOk;
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <returns>返回执行结果</returns>
        public string GetStoreFileType(FileUploadStruct fileUpload)
        {
            string strSql = string.Format(@"select {0} from {1} where {2}='" + fileUpload.KeyIdValue + "'",
                fileUpload.FileTypeFieldName, fileUpload.TableName, fileUpload.KeyId);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 设置用途
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">id值</param>
        /// <param name="strUseType">用途</param>
        public void SetUseType(string strTableName, string strKeyId, string strUseType)
        {
            string strSql = string.Format(@"update {0} set USE_TYPE={1} where ID='{2}'",
                strTableName, strUseType, strKeyId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 删除存储文件信息
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">键值名</param>
        /// <param name="strKeyIdValue">键值</param>
        public void DeleteStoreFile(string strTableName, string strKeyId, string strKeyIdValue)
        {
            string strSql = string.Format(@"delete from {0} where {1}='" + strKeyIdValue + "'",
                strTableName, strKeyId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取上传的资源信息
        /// </summary>
        /// <param name="strResourceName">查找的资源名</param>
        /// <param name="strUseType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--添加流程ID条件 edit by zhongjian 20091026-->
        public DataTable GetResourceInfo(string strResourceName, string strUseType,string strFlowID)
        {
            DataTable dtTemp;
            string strSql = @"select  rownum,a.* from (select id,flowid,TABLE_NAME,RESOURCE_NAME,RESOURCE_TYPE,USE_TYPE,ORDERDIELD,
                wynewpackage.GetItemTypeName(USE_TYPE,t.paramid) rstype from SYS_RESOURCE t where 1=1";

            if (!string.IsNullOrEmpty(strResourceName))
            {
                strSql += string.Format(" and RESOURCE_NAME like '%{0}%'", strResourceName);
            }
            if (!string.IsNullOrEmpty(strUseType))
            {
                strSql += string.Format(" and USE_TYPE='{0}'", strUseType);
            }
            if (!string.IsNullOrEmpty(strFlowID))
            {
                strSql += string.Format(" and flowid='{0}'", strFlowID);
            }

            strSql += "order by ORDERDIELD) a";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetStoreFile(string strTabName, string strKeyId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select id, RESOURCE_CONTENT, RESOURCE_NAME,RESOURCE_TYPE from {0} where id='" + strKeyId + "'",
                strTabName);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取附件类别信息
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public DataTable GetParams(string strName)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select name,keyvalue from SYS_PARAMS where name='" + strName + "'");

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 增加材料
        /// </summary>
        /// <param name="strModuleName">模板名称</param>
        /// <param name="strType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <param name="strMax">排序字段</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public static void InsertModule(string strModuleName, string strType, string strFlowID, string strMax)
        {
            if (!string.IsNullOrEmpty(strModuleName))
            {
                string strSql = string.Format("Insert into XT_SERIAL_MODULE (modulename,type,flowid,orderdield) VALUES ('{0}','{1}','{2}','{3}')", strModuleName, strType, strFlowID, strMax);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 修改材料
        /// </summary> 
        /// <param name="strId">模板ID</param>
        /// <param name="strModuleName">模板名称</param>
        /// <param name="strType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public static void UpdateModule(string strId, string strModuleName, string strType, string strFlowID)
        {
            if (!string.IsNullOrEmpty(strModuleName))
            {
                string strSql = string.Format("Update XT_SERIAL_MODULE set modulename='{1}',type='{2}',flowid='{3}' where id='{0}'", strId, strModuleName, strType, strFlowID);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 删除材料
        /// </summary>
        /// <param name="strId"></param>
        public static void DeleteModule(string strId)
        {
            string strSql = string.Format("delete from XT_SERIAL_MODULE where id='{0}'", strId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 清空材料
        /// </summary>附件
        /// <param name="strId"></param>
        public static void EmptyModule(string strId)
        {
            string strSql = string.Format("Update XT_SERIAL_MODULE set FILEDATA='',FILETYPE='',FILENAME='' where id='{0}'", strId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取材料
        /// </summary>
        /// <returns></returns>
        public DataTable GetModule()
        {
            DataTable dtTemp;
            string strSql = @"select * from XT_SERIAL_MODULE order by type asc";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据条件获取材料
        /// </summary>
        /// <param name="strId"></param>
        /// <returns></returns>
        public DataTable GetModuleId(string strId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select * from XT_SERIAL_MODULE where id = '{0}'",strId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据模板类别获取材料
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public DataTable GetModuleType(string strFlowID)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select * from XT_SERIAL_MODULE where flowid = '{0}' order by orderdield", strFlowID);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 在线申报文件上传信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <param name="dType">数据存储类型</param>
        /// <param name="strModuleId">模块Id</param>
        /// <returns>返回执行结果</returns>
        public void StoreCailiaoUploadFile(FileUploadStruct fileUpload, DataStoreType dType)
        {
            string strSql = string.Empty;

            if (dType == DataStoreType.Update)
            {
                strSql = string.Format(@"update {0} set {1}=:filecontent,{2}='"
                  + fileUpload.FileTypeFieldValue + "',{4}='" + fileUpload.StoreResourceNameValue 
                  + "' where {3}='" + fileUpload.KeyIdValue + "'",fileUpload.TableName, 
                  fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName,
                  fileUpload.KeyId, fileUpload.StoreResourceFieldName);
            }
            if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4},MODULENAME) values(:filecontent,'"
                    + fileUpload.FileTypeFieldValue + "','" + fileUpload.StoreKeyValue
                    + "','" + fileUpload.StoreResourceNameValue + "','" + fileUpload.StoreResourceNameValue 
                    + "')", fileUpload.TableName, fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName,
                    fileUpload.StoreKeyFieldName, fileUpload.StoreResourceFieldName, fileUpload.StoreResourceFieldName);
            }

            System.Data.IDataParameter[] idp = new System.Data.OracleClient.OracleParameter[1];
            idp[0] = new OracleParameter(":filecontent", OracleType.Blob);
            idp[0].Value = fileUpload.FileBytes;
            //idp[1] = new OracleParameter(":newId", OracleType.VarChar, 32);
            //idp[1].Direction = ParameterDirection.Output;
            SysParams.OAConnection().RunSql(strSql, ref idp);
            //fileUpload.KeyIdValue = idp[1].Value.ToString();
        }

        /// <summary>
        /// 获取在线申报文件上传的资源信息
        /// </summary>
        /// <param name="strFileName">查找的文件名</param>
        /// <param name="strId">模板id</param>m>
        /// <returns></returns>
        public DataTable GetCailiaoResourceInfo(string strFileName, string strId)
        {
            DataTable dtTemp;
            string strSql = @"select id,MODULENAME,FILEDATA,FILETYPE,FILENAME from XT_SERIAL_MODULE t";

            if (!string.IsNullOrEmpty(strFileName))
            {
                strSql += string.Format(" and FILENAME like '%{0}%'", strFileName);
            }
            if (!string.IsNullOrEmpty(strId))
            {
                strSql += string.Format(" and id='{0}'", strId);
            }

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取在线申报文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetCailiaoStoreFile(string strTabName, string strKeyId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select id,FILEDATA,FILENAME,FILETYPE from {0} where id='{1}'",
                 strTabName,strKeyId);
 
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取表格下载排序最大值
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--update by zhongjian 20091018-->
        public static string GetMax(string strFlowID)
        {
            string strCount = "";
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"select max(ORDERDIELD) from sys_resource where flowid='{0}'", strFlowID);
                strCount = SysParams.OAConnection().GetValue(strSql);
            }
            return strCount;
        }

        /// <summary>
        /// 按条件获取材料排序最大值
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--add by zhongjian 20091018-->
        public static string GetModuleMax(string strFlowID)
        {
            string strCount = "";
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"select max(ORDERDIELD) from XT_SERIAL_MODULE where flowid='{0}'", strFlowID);
                strCount = SysParams.OAConnection().GetValue(strSql);
            }
            return strCount;
        }

        /// <summary>
        /// 获取表单排序最大值
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--add by zhongjian 20091026-->
        public static string GetProcessMax(string strFlowID)
        {
            string strCount = "";
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"select max(ORDERDIELD) from XT_PROCESS where flowid='{0}'", strFlowID);
                strCount = SysParams.OAConnection().GetValue(strSql);
            }
            return strCount;
        }

        /// <summary>
        /// 获取权限申请信息
        /// </summary>
        /// <param name="SerialID">ID</param>
        /// <returns></returns>
        /// <!--add by zhongjian 20091221-->
        public DataTable GetUserSerial(string SerialID)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select a.id,a.userid,b.username,a.serial_name,a.notes,a.is_pass,a.serial_time 
                from sys_user_serial a left join sys_user b on a.userid = b.userid where a.id='{0}'",SerialID);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">流程权限ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091221-->
        public DataTable GetSerialAtt(string strSerialId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select id,filedata, filename,filetype
                            from sys_user_serialatt where serial_id= '{0}'", strSerialId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">附件ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091221-->
        public DataTable GetUserSerialAtt(string strId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select id,filedata, filename,filetype
                            from sys_user_serialatt where id= '{0}'", strId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取在线申报上传的资源信息
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091229-->
        public DataTable GetCailiaoInfo(string strFlowName, string strNewsId)
        {     
            DataTable dtTemp;
            string strSql = string.Format(@"select rownum 序号,t.* from (select a.id as moduleid,a.modulename,a.type,
                a.filedata as mudulefiledata,a.filename||'.'||a.filetype as mudulefilename,
                b.id as attachid,b.AppDataId,b.filedata as attachfiledata,
                b.filename||'.'|| b.filetype as attachfilename, b.flag,b.filename attfilename
                from (select * from XT_SERIAL_MODULE where type = '{0}') a,
                (select * from XT_SERIAL_ATTACHMENT where AppDataId = '{1}') b
                 where a.Id=b.Module_Id(+) ORDER BY a.ORDERDIELD) t", strFlowName, strNewsId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 更新记录排序
        /// </summary>
        /// <param name="strCurrentNo">当前序号</param>
        /// <param name="strFlowId">类型id</param>
        /// <param name="strResourceId">资源id</param>
        /// <param name="nSortFlag">排序标志（0：降序，1：升序）</param>
        public void UpdateSort(string strCurrentNo,string strFlowId,string strResourceId,int nSortFlag)
        {
            int NewNo = Convert.ToInt32(strCurrentNo) - 1;

            if (nSortFlag == 0)//降序
            {
                NewNo--;
            }
            else//升序
            {
                NewNo++;
            }

            //将新序号对应记录更新为当前序号
            string strSql = string.Format("update sys_resource set ORDERDIELD={0} where ORDERDIELD={1} and flowid ='{2}'", strCurrentNo, NewNo,strFlowId);
            SysParams.OAConnection().RunSql(strSql);

            //将当前序号更新为新序号
            strSql = string.Format("update sys_resource set ORDERDIELD={0} where id = '{1}'", NewNo, strResourceId);
            SysParams.OAConnection().RunSql(strSql);
        }
    }
}
